/*
 * GenericDAO
 * 
 * CRUD on database based on object metadata.
 * 
 */
package br.unirio.bsi.dsw.AuctionSite.model.dao;

import br.unirio.bsi.dsw.AuctionSite.model.Estilo;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import br.unirio.bsi.dsw.AuctionSite.model.fetch_parameter;
import java.sql.Statement;
import java.util.ArrayList;

public class GenericDAO
{ 
  // url jdbc para conexao com o postgres
  private static final String conUrl = "jdbc:postgresql://localhost/LEILAO?user=postgres&password=postgres";

  // POSTGRES CONSTANTS
  public static final String UNIQUE_VIOLATION = "23505";
  
  // singleton
  private static GenericDAO instance = null;
 
  // generic singleton
  public static GenericDAO getInstance() throws InstantiationException
  {
    if( instance == null ) 
    {
      try {
        Class.forName("org.postgresql.Driver");
        
      } catch( ClassNotFoundException e ){
        throw new InstantiationException( e.getMessage() ); 
      }
      
      instance = new GenericDAO();
    }
    return instance;
  }
 
  public void execute(String s) throws SQLException 
  {    
    Connection conn = DriverManager.getConnection(conUrl);    
    conn.createStatement().execute(s);    
    conn.close();
  }

  public void deleteAll( Class<?> klazz ) throws SQLException  {
    execute( "DELETE FROM " + klazz.getSimpleName() ) ;   
  }

  public void insert( Object o ) throws SQLException,
                                        NoSuchMethodException,
                                        IllegalAccessException,
                                        InvocationTargetException 
  {
    Class<?> klazz = o.getClass();
    
    while(  !klazz.getSuperclass().equals(Object.class) ) {            
      klazz = klazz.getSuperclass();
    }
    
    String sql = "INSERT INTO " + klazz.getSimpleName() + " VALUES (";
           
    Field fields[] = klazz.getDeclaredFields();
    for( Field f : fields )
    {
      fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
      if( fp != null && fp.sql_order() != -1 )
      {
        if( fp.sql_key() == false )
          sql += "?,";                
        else
        if( fp.sql_key() == true )
          sql += "nextval('"+klazz.getSimpleName()+"_"+f.getName()+"_seq'),";
      }
    }
    sql += ");";  
    sql = sql.replace(",)", ")");
       
    Connection conn = DriverManager.getConnection(conUrl);
    PreparedStatement ps = conn.prepareStatement( sql );
    for( Field f : fields )
    {
      fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
      if( fp != null && fp.sql_order() != -1 && !fp.sql_key() ) 
      { 
        try {
          Object t = klazz.getDeclaredMethod( "get"+f.getName() ).invoke(o);
          
          if( f.getType() == Integer.class )
            ps.setInt( fp.sql_order(), (Integer)t );
          else
          if( f.getType() == String.class )
            ps.setString( fp.sql_order(), (String)t );
        }
        catch( NoSuchMethodException e ){} 
        catch( IllegalAccessException e ){} 
        catch( InvocationTargetException e ){}             
      }      
    }
    
    ps.execute();
    conn.close();
  }

  public Object retrieve( Object o ) throws SQLException
  {
    Class<?> klazz = o.getClass();   
    
    while(  !klazz.getSuperclass().equals(Object.class) ) {            
      klazz = klazz.getSuperclass();
    }
    
    String sql = "SELECT * FROM " + klazz.getSimpleName() + " WHERE ";
    
    Field fields[] = klazz.getDeclaredFields();
    for( Field f : fields )
    {
      fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
      if( fp != null && fp.sql_order() != -1 )
      {
        try 
        {
          Object t = klazz.getDeclaredMethod( "get"+f.getName() ).invoke( o );
          
          if( t != null ) 
          {
            sql += f.getName();            
            if( f.getType() == Integer.class )
              sql += "="+String.valueOf( t )+" AND "; 
            else
            if( f.getType() == String.class )
              sql += "='"+t+"' AND "; // sql injection prone
          }
        }
        catch( Exception e ){} 
      }      
    }
    sql += "1=1";

    Connection conn = DriverManager.getConnection(conUrl);
    Statement stmt = conn.createStatement();
    
    ResultSet rs = stmt.executeQuery( sql );
    
    if( rs.next() )
    {
      do
      {
        for( Field f : fields )
        {
          fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
          if( fp != null && fp.sql_order() != -1 ) 
          { 
            try 
            {
              Class param[] = new Class[]{f.getType()};
              Method met = klazz.getDeclaredMethod( "set"+f.getName(), param );

              if( f.getType() == Integer.class )
                met.invoke( o, rs.getInt( f.getName() ) );
              else
              if( f.getType() == String.class )
                met.invoke( o, rs.getString( f.getName() ) );
            }
            catch( NoSuchMethodException e ){} 
            catch( IllegalAccessException e ){} 
            catch( InvocationTargetException e ){}             
          }      
        }
      }
      while(rs.next());
    }
    else
      o = null;

    conn.close();
        
    return o;
  }

  public void update(Object o) throws SQLException
  {
    Class<?> klazz = o.getClass();
    
    while( !klazz.getSuperclass().equals(Object.class) ) {            
      klazz = klazz.getSuperclass();
    }
    
    String sql = "UPDATE " + klazz.getSimpleName() + " SET ";
    
    Field fields[] = klazz.getDeclaredFields();
    for( Field f : fields )
    {
      fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
      if( fp != null && fp.sql_order() != -1 && !fp.sql_key() )
      {
        try 
        {
          Object t = klazz.getDeclaredMethod( "get"+f.getName() ).invoke( o );
          
          if( t != null ) 
          {
            sql += f.getName();            
            if( f.getType() == Integer.class )
              sql += "="+String.valueOf( t )+", "; 
            else
            if( f.getType() == String.class )
              sql += "='"+t+"', "; //sql injection prone              
          }
        }
        catch( Exception e ){} 
      }      
    }
    sql = sql.substring(0, sql.length() - 2) + " WHERE ";
    
    for( Field f : fields )
    {
      fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
      if( fp != null && fp.sql_order() != -1 && fp.sql_key()==true )
      {
        try 
        {
          Object k = klazz.getDeclaredMethod( "get"+f.getName() ).invoke( o );
          if( k != null )
          {
            sql += f.getName();            
            if( f.getType() == Integer.class )
              sql += "="+String.valueOf( k )+" AND "; 
            else
            if( f.getType() == String.class )
              sql += "='"+k+"' AND "; //sql injection prone
          }
        }
        catch( Exception e ){}
      }
    }
    sql = sql.substring(0, sql.length() - 5);
    
    execute( sql );
  }
  
  public void retrieveAll( String sqlQuery, Class klazz, ArrayList objs ) throws SQLException
  {
    Connection conn = DriverManager.getConnection(conUrl);
    Statement stmt = conn.createStatement();    
    ResultSet rs = stmt.executeQuery( sqlQuery );
    
    if( rs.next() )      
    {
      try
      {        
        do
        {
          Object o = klazz.newInstance();
          Class  oc = o.getClass();
          do
          {                    
            Field fields[] = oc.getDeclaredFields();          
            for( Field f : fields )
            {
              fetch_parameter fp = f.getAnnotation( fetch_parameter.class );
              if( fp != null && fp.sql_order() != -1 ) 
              { 
                try 
                {
                  Class param[] = new Class[]{f.getType()};
                  Method met = oc.getDeclaredMethod( "set"+f.getName(), param );

                  if( f.getType() == Integer.class )
                    met.invoke( o, rs.getInt( f.getName() ) );
                  else
                  if( f.getType() == String.class )
                    met.invoke( o, rs.getString( f.getName() ) );
                }
                catch( NoSuchMethodException e ){} 
                catch( IllegalAccessException e ){} 
                catch( InvocationTargetException e ){}
              }      
            }
            oc = oc.getSuperclass();
          }
          while( oc != null );
          
          objs.add( o );
        }
        while(rs.next());
      }
      catch( Exception e){}
    }

    conn.close();          
  }

}
